﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using Newtonsoft.Json;
using PDAMain.Entity;
using System.Text.RegularExpressions;
using PDAMain.Interface;

namespace PDAMain.DAL.Dao
{
    /// <summary>
    /// 客服工單表
    /// 表名：SERVICE_RECEIPT
    /// 欄位名稱：
    /// CWOrder_No 客服工單號
    /// Event_Level 工單優先級
    /// Status 狀態
    /// address 地址
    /// user_name 聯繫人
    /// user_phone 聯繫人電話
    /// Description 描述
    /// *****dutyPerson 負責人
    /// Comment 備註
    /// SR_NUMBER 服务请求编号
    
    /// </summary>
    class CustomerWorkDao : BaseDao
    {
        public string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase.ToString());
        public string dbName = "Sample.db3";
        public string sql = string.Empty;

        /// <summary>
        /// 获取待处理客服工单
        /// </summary>
        /// <returns>待处理客服工单列表</returns>
        public DataSet list() 
        {
            updateDataFromWebservice();
            DataSet customerListDt = new DataSet();
            string str_Sql = "select CWOrder_No,Address,Event_Level, case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '9' then '已处理' else '无法识别' end as STATUS from CustomerWork  where STATUS ='1'";
            customerListDt = base.executeDataSet(str_Sql);
            return customerListDt;
        }
        /// <summary>
        /// 获取待处理客服工单数量
        /// </summary>
        /// <returns></returns>
        public string dataNum()
        {
            this.updateDataFromWebservice();
            string num = "0";
            string str_Sql = "select count(*) from CustomerWork where STATUS='1'";
            num = base.executeDataSet(str_Sql).Tables[0].Rows[0][0].ToString();
            if (Convert.ToInt32(num) > 99) num = "99+";
            return num;

        }
        /// <summary>
        ///简单查询—— 查询符合条件的客服工单
        /// </summary>
        /// <param name="status">状态</param>
        /// <param name="time">时间</param>
        /// <returns>符合条件的客服工单</returns>
        public DataSet select(string status,string time) 
        {
            DataSet customerSelDt = new DataSet();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string strData = pda.queryWorkOrder("", status, "", "", "", time, DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"), Identity.dutyPerson);
            customerSelDt = base.convertToDataSet(strData);
            return customerSelDt;
        }
        /// <summary>
        /// 高级查询—— 查询符合条件的客服工单
        /// </summary>
        /// <param name="customerID">客服工单号</param>
        /// <param name="connection">联系人</param>
        /// <param name="telphoneNO">联系人电话</param>
        /// <param name="discription">问题描述</param>
        /// <param name="dateStart">起始时间</param>
        /// <param name="dateEnd">结束时间</param>
        /// <returns>符合条件的客服工单</returns>
        public DataSet complexSel(string customerID,string connection,string telphoneNO,string description,string dateStart,string dateEnd) 
        {
            DataSet complexSelDt = new DataSet();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string strData = pda.queryWorkOrder(customerID, "", connection, telphoneNO, description, dateStart, dateEnd, Identity.dutyPerson);
            if (strData == string.Empty)
                return null;
            complexSelDt = base.convertToDataSet(strData);
            return complexSelDt;
        }
        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="customerID">客服工单号</param>
        /// <returns></returns>
        public DataSet getDetail(string customerID)
        {
            DataSet customerDetailDt = new DataSet();
            string str_Sql = "select * from CustomerWork where CWOrder_No='" + customerID + "'";
            //customerDetailDt = PublicMethods.SelectData(str_Sql);
            customerDetailDt = base.executeDataSet(str_Sql);
            return customerDetailDt;
        }
        /// <summary>
        /// 客服工单处理完成，转单，报修，现场工单均调用此方法更改工单状态
        /// </summary>
        /// <param name="customerID">客服工单号</param>
        /// <param name="status">状态</param>
        /// <param name="time">处理时间</param>
        /// <param name="remark">处理时间</param>
        public bool deal(string customerID, string status, string time, string remark)
        {
            //此處處理狀態需同時在本地數據庫和Web端更新狀態
            //转单，报修，完成，现场工单操作相同
            bool Flag = false;
            pdaWebReference.PdaService pda = base.getPdaWebService();
            try
            {
                Flag = pda.updateWorkOrder(customerID, status, time, remark, Identity.dutyPerson);
                string str_Sql = "";

                //Flag为true(webservice更新成功)，则删除本地该条数据，为False(webservice更新失败)，则更新更新数据
                if (Flag)
                {
                    str_Sql = "delete from CustomerWork where CWOrder_No='" + customerID + "'";
                }
                Flag = base.executeNonQuery(str_Sql);
                return Flag;
            }
            catch
            {
                return Flag;
            }
        }

        /// <summary>
        /// 同步时判断本地数据库是否相同的记录，有则不同步
        /// </summary>
        /// <param name="activityUID">客服工单编号</param>
        /// <returns></returns>
        public bool verifyData(string activityUID)
        {
            bool result = false;
            string str_Sql = "select 1 from CustomerWork where CWOrder_No='" + activityUID + "'";
            DataSet ds = base.executeDataSet(str_Sql);
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    result = true;
                return result;
            }
            catch
            {
                return result;
            }
           
        }

        /// <summary>
        /// 同步數據
        /// </summary>
        /// <param name="dr"></param>
        public bool insertData(DataRow dr)
        {
            bool successFlag = false;
            string str_Sql = "insert into CustomerWork(CWOrder_No,Address,User_Name,User_Phone,DESCRIPTION,Event_Level,STATUS,Comment,SR_NUMBER,UPDATEDT) values("
                + "'" + dr["ACTIVITYUID"] + "','" + dr["HOUSELOCATION"] + "','"
                + dr["CONTACTPERSON"] + "','" + dr["CONTACTPHONENO"] + "','"
                + dr["workDESCRIPTION"] + "','" + dr["workPRIORITY"] + "','"
                + dr["workSTATUS"] + "','" + dr["COMMENT"] + "','"
                + dr["SRNO"] + "','" + DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + "')";
            try
            {
                successFlag = base.executeNonQuery(str_Sql);
                return successFlag;
            }
            catch
            {
                return successFlag;
            }
        }

        /// <summary>
        /// 通过webservice同步PDA数据
        /// </summary>
        public void updateDataFromWebservice()
        {
            //同步數據
            //從Webservice同步數據
            DataTable dt = new DataTable();
            if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            {
                pdaWebReference.PdaService pda = base.getPdaWebService();
                string str = pda.listWorkOrder(Identity.dutyPerson);
                if (str == string.Empty)
                    return;
                DataSet ds = base.convertToDataSet(str);
                if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                    return;
                dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //驗證系統中是否存在該筆數據
                    string strActivity_UID = dt.Rows[i]["ACTIVITYUID"].ToString();
                    if (verifyData(strActivity_UID))
                    {
                        try
                        {
                            insertData(dt.Rows[i]);
                        }
                        catch (Exception ex) { }
                    }
                }
            }
        }

        /// <summary>
        /// 状态转换
        /// </summary>
        private static string[,] ListArray = new string[,] {
        {"1","处理中"},
        {"2","转单"},
        {"3","报修"},
        {"9","已处理"}
        };

        /// <summary>
        /// 通过code获取描述
        /// </summary>
        /// <param name="code">code</param>
        /// <returns></returns>
        public string getShow(string code)
        {
            string strResult = string.Empty;
            if (code == string.Empty)
                return strResult;
            for (int i = 0; i < ListArray.GetLength(0); i++)
            {
                if (code == ListArray.GetValue(i, 0).ToString())
                {
                    strResult = ListArray.GetValue(i, 1).ToString();
                    break;
                }
            }
            return strResult;
        }

        /// <summary>
        /// 验证数据类型
        /// </summary>
        /// <param name="strVerifyData">验证的数据</param>
        /// <param name="strVerifyType">数据类型</param>
        /// <returns></returns>
        public bool verifyData(string strVerifyData, string strVerifyType)
        {
            bool Flag = false;
            if (strVerifyData == string.Empty)
                return true;
            try
            {
                switch (strVerifyType.ToLower())
                {
                    case "int":
                        {
                            string s = "^[0-9]*$";//正则表达式
                            Regex reg = new Regex(s);
                            if (reg.IsMatch(strVerifyData))
                            {
                                Flag = true;
                            }
                            //int i = Convert.ToInt32(strVerifyData);
                            //Flag = true;
                            break;
                        }
                    case "date":
                        {
                            DateTime dt = Convert.ToDateTime(strVerifyData);
                            Flag = true;
                            break;
                        }
                    default:
                        { Flag = true; break; }
                }
                return Flag;
            }
            catch(Exception ex)
            {
                return Flag;
            }
        }

        public DataSet CustomerSelect(string strStatus)
        {
            DataSet ds = null;
            string str_Sql = "select CWOrder_No as activityUID,Address as houseLocation,Event_Level as workPriority,"
            + "case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '9' then '已处理' else '无法识别'"
            + "end as workStatus,UPDATEDT from CustomerWork where STATUS='" + strStatus + "'";
            ds = base.executeDataSet(str_Sql);
            return ds;
        }

        public DataSet CustomerComplexSel(string customerID, string strContactName, string strPhone, string strDescription)
        {
            DataSet ds = null;
            string str_Sql = "select CWOrder_No as activityUID,Address as houseLocation,Event_Level as workPriority,"
                + "case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '9' then '已处理' else '无法识别'"
                + "end as workStatus,UPDATEDT from CustomerWork where CWOrder_No like '%" + customerID + "%' and User_Name like '%"
                + strContactName + "%' and User_Phone like '%" + strPhone + "%' and DESCRIPTION like '%" + strDescription + "%'";
            ds = base.executeDataSet(str_Sql);
            return ds;
        }

        public DataSet CustomerList()
        {
            DataSet ds = null;
            string str_Sql = "select * from CustomerWork";
            ds = base.executeDataSet(str_Sql);
            return ds;
        }

        public bool CustomerDrop()
        {
            bool result = false;
            string str_Sql = "drop table CustomerWork";
            result = base.executeNonQuery(str_Sql);
            return result;
        }

        public bool FastWorkOrder(string customerID, string status, string time, string remark, string locateId, string barCode,string estdur)
        {
            //此處處理狀態需同時在本地數據庫和Web端更新狀態
            bool Flag = false;
            string recordeId = Identity.dutyPerson;
            if (this.updateWorkOrderStatus(customerID, status, remark, time) && insertApplyRepair(barCode, recordeId, time, remark, locateId,estdur))
            {

                deleteLocalData(customerID);//删除本地库的相关记录
                Flag = true;
            }/*
            else
            {
                string str_Sql = "update CustomerWork set Comment='" + remark + "', "
                         + "status='" + status + "', "
                         + "updatedt='" + time + "' where CWOrder_No='" + customerID + "'";
                base.executeNonQuery(str_Sql);
                DataHelper.ApplyToRepairDao.insertLacolTable(barCode, recordeId, time, remark, "", "0", locateId);
                 Flag = true;
            }*/
            return Flag;
        }

        public bool updateWorkOrderStatus(string customerID, string status, string comment, string time)
        {
            if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            {
                try
                {
                    //鏈接webService接口
                    pdaWebReference.PdaService pda = base.getPdaWebService();
                    return pda.updateWorkOrder(customerID, status, time, comment, Identity.dutyPerson);
                        
                    
                }
                catch (Exception ex)
                {
                    return false;
                }

            }
            else
            {
                return false;


            }
        }

        public bool insertLacolTable(string customerID, string status, string updateTime, string remark)
        {
            bool result = false;
            string str_sql = "update CustomerWork set Comment='" + remark + "', "
                         + "status='" + status + "', "
                         + "updatedt='" + updateTime + "' where CWOrder_No='" + customerID + "'";
            result = base.executeNonQuery(str_sql);
            return result;
        }
        public bool insertApplyRepair(string barCode, string recordeId, string applyTime, string remark,string locateId,string estdur)
        {
            bool result = false;
            if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            {
                pdaWebReference.PdaService pda = base.getPdaWebService();
                result = pda.insertApplyRepair(barCode, recordeId, applyTime, remark, "", locateId, "0", estdur);

            }
            return result;
        }
        public void deleteLocalData(string customerID)
        {
            executeNonQuery("delete from CustomerWork where CWOrder_No='" + customerID + "'");
        }

    }
}
